package busterminal.persistence.jdbc;

import busterminal.entity.ScheduledItem;
import busterminal.entity.ScheduledItemImplementation;
import busterminal.entity.service.DaysOfWeek;
import busterminal.entity.service.Time;
import busterminal.exception.BusinessException;
import busterminal.exception.DatabaseException;
import busterminal.persistence.ScheduledItemDAO;
import busterminal.persistence.jdbc.proxy.RouteProxy;
import busterminal.persistence.jdbc.proxy.ScheduledItemProxy;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Реализация интерфейса Data Access Object для пункта расписания для работы с базой данных
 */
public class JdbcScheduledItemDAO extends AbstractJdbcDAO<ScheduledItem> implements ScheduledItemDAO {

    /**
     * Добавить пункт расписания в базу данных
     * @param entity ссылка на пункт расписания
     */
    @Override
    public void create(ScheduledItem scheduledItem) {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement("INSERT INTO ScheduledItem (Id, RouteId, DepartureTime, ArrivalTime, DepartureTimeFromEndPoint, ArrivalTimeToBusTerminal, PlatformNumber) VALUES (?, ?, ?, ?, ?, ?, ?)");
            scheduledItem.setId(getNextId());
            preparedStatement.setInt(1, scheduledItem.getId());
            preparedStatement.setInt(2, scheduledItem.getRoute().getId());
            preparedStatement.setString(3, scheduledItem.getDepartureTime().toString());
            preparedStatement.setString(4, scheduledItem.getArrivalTime().toString());
            preparedStatement.setString(5, scheduledItem.getDepartureTimeFromEndPoint().toString());
            preparedStatement.setString(6, scheduledItem.getArrivalTimeToBusTerminal().toString());
            preparedStatement.setInt(7, scheduledItem.getPlatformNumber());
            preparedStatement.execute();
            preparedStatement = connection.prepareStatement("INSERT INTO RoutingDays (ScheduledItemId, DayOfWeek, DayOrder) VALUES (?, ?, ?)");
            int scheduledItemId = scheduledItem.getId();
            int counter = 1;
            for (DaysOfWeek dayOfWeek : scheduledItem.getRoutingDays()) {
                preparedStatement.setInt(1, scheduledItemId);
                preparedStatement.setString(2, dayOfWeek.toString());
                preparedStatement.setInt(3, counter);
                preparedStatement.execute();
                counter++;
            }
        } catch (SQLException e) {
            throw new DatabaseException("Cannot create new scheduled item: " + scheduledItem, e);
        } finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cannot close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    /**
     * Прочитать пункт расписания из базы данных
     * @param id идентификатор пункта расписания
     * @return ссылка на прочитанный пункт расписания
     */
    @Override
    public ScheduledItem read(Integer id) {
        Connection connection = getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        ScheduledItem scheduledItem = null;
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT Id, RouteId, DepartureTime, ArrivalTime, DepartureTimeFromEndPoint, ArrivalTimeToBusTerminal, PlatformNumber FROM ScheduledItem WHERE Id=" + id);
            if (resultSet.next()) {
                scheduledItem = new ScheduledItemImplementation();
                scheduledItem.setId(resultSet.getInt("Id"));
                int routeId = resultSet.getInt("RouteId");
                if (resultSet.getInt("RouteId") != 0) {
                    scheduledItem.setRoute(new RouteProxy(routeId));
                }
                scheduledItem.setDepartureTime(Time.parseTime(resultSet.getString("DepartureTime")));
                scheduledItem.setArrivalTime(Time.parseTime(resultSet.getString("ArrivalTime")));
                scheduledItem.setDepartureTimeFromEndPoint(Time.parseTime(resultSet.getString("DepartureTimeFromEndPoint")));
                scheduledItem.setArrivalTimeToBusTerminal(Time.parseTime(resultSet.getString("ArrivalTimeToBusTerminal")));
                scheduledItem.setPlatformNumber(resultSet.getInt("PlatformNumber"));
                resultSet = statement.executeQuery("SELECT DayOfWeek FROM RoutingDays WHERE ScheduledItemId=" + scheduledItem.getId() + " ORDER BY DayOrder");
                List<DaysOfWeek> routingDays = new ArrayList<DaysOfWeek>();
                while (resultSet.next()) {
                    routingDays.add(DaysOfWeek.valueOf(resultSet.getString("DayOfWeek").toUpperCase()));
                }
                scheduledItem.setRoutingDays(routingDays);
            }
        } catch (SQLException e) {
            throw new DatabaseException("Cannot read scheduled item with Id=" + id, e);
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cannot close statement");
                ex.printStackTrace(System.err);
            }
        }
        if (scheduledItem == null) {
            throw new BusinessException("Scheduled item with Id=" + id + " not found");
        }
        scheduledItem.setTravelTime();
        return scheduledItem;
    }

    /**
     * Обновить сведения о пункте расписания в базе данные
     * @param entity ссылка на пункт расписания
     */
    @Override
    public void update(ScheduledItem scheduledItem) {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement("UPDATE ScheduledItem SET RouteId=?, DepartureTime=?, ArrivalTime=?, DepartureTimeFromEndPoint=?, ArrivalTimeToBusTerminal=?, PlatformNumber=? WHERE Id=?");
            preparedStatement.setInt(1, scheduledItem.getRoute().getId());
            preparedStatement.setString(2, scheduledItem.getDepartureTime().toString());
            preparedStatement.setString(3, scheduledItem.getArrivalTime().toString());
            preparedStatement.setString(4, scheduledItem.getDepartureTimeFromEndPoint().toString());
            preparedStatement.setString(5, scheduledItem.getArrivalTimeToBusTerminal().toString());
            preparedStatement.setInt(6, scheduledItem.getPlatformNumber());
            preparedStatement.setInt(7, scheduledItem.getId());
            preparedStatement.execute();
            preparedStatement = connection.prepareStatement("DELETE FROM RoutingDays WHERE ScheduledItemId=?");
            preparedStatement.setInt(1, scheduledItem.getId());
            preparedStatement.execute();
            preparedStatement = connection.prepareStatement("INSERT INTO RoutingDays (ScheduledItemId, DayOfWeek, DayOrder) VALUES (?, ?, ?)");
            int scheduledItemId = scheduledItem.getId();
            int counter = 1;
            for (DaysOfWeek dayOfWeek : scheduledItem.getRoutingDays()) {
                preparedStatement.setInt(1, scheduledItemId);
                preparedStatement.setString(2, dayOfWeek.toString());
                preparedStatement.setInt(3, counter);
                preparedStatement.execute();
                counter++;
            }
        } catch (SQLException e) {
            throw new DatabaseException("Cannot update scheduled item with Id=" + scheduledItem.getId(), e);
        } finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cannot close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    /**
     * Удалить пункт расписания из базы данных
     * @param entity ссылка на пункт расписания
     */
    @Override
    public void delete(ScheduledItem scheduledItem) {
        delete(scheduledItem.getId());
    }

    /**
     * Удалить пункт расписания из базы данных
     * @param id идентификатор пункта расписания
     */
    @Override
    public void delete(Integer id) {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement("DELETE FROM RoutingDays WHERE ScheduledItemId=?");
            preparedStatement.setInt(1, id);
            preparedStatement.execute();
            preparedStatement = connection.prepareStatement("DELETE FROM ScheduledItem WHERE Id=?");
            preparedStatement.setInt(1, id);
            preparedStatement.execute();
        } catch (SQLException e) {
            throw new DatabaseException("Cannot delete scheduled item with Id=" + id, e);
        } finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cannot close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    /**
     * Получить список всех пунктов расписания из базы данных
     * @return список пунктов расписания
     */
    @Override
    public List<ScheduledItem> findAll() {
        Connection connection = getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        List<ScheduledItem> scheduledItemList = new ArrayList<ScheduledItem>();
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT Id FROM ScheduledItem");
            while (resultSet.next()) {
                ScheduledItem scheduledItem = new ScheduledItemProxy();
                scheduledItem.setId(resultSet.getInt("Id"));
                scheduledItemList.add(scheduledItem);
            }
        } catch (SQLException e) {
            throw new DatabaseException("Cannot read scheduled item list", e);
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cannot close statement");
                ex.printStackTrace(System.err);
            }
        }
        return scheduledItemList;
    }

    /**
     * Найти пункты расписания по дням недели осуществления маршрутов
     * @param routingDays дни недели осуществления маршрутов
     * @return список найденных пунктов расписания
     */
    @Override
    public List<ScheduledItem> findByRoutingDays(List<DaysOfWeek> routingDays) {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<ScheduledItem> scheduledItemList = new ArrayList<ScheduledItem>();
        if (!routingDays.isEmpty()) {
            int routingDaysSize = routingDays.size();
            StringBuilder query = new StringBuilder("SELECT ScheduledItemId FROM RoutingDays WHERE DayOfWeek IN (");
            for (int i = 0; i < routingDaysSize; i++) {
                if (i != routingDaysSize - 1) {
                    query.append("?, ");
                } else {
                    query.append("?) GROUP BY ScheduledItemId");
                }
            }
            try {
                preparedStatement = connection.prepareStatement(query.toString());
                for (int i = 1; i <= routingDaysSize; i++) {
                    preparedStatement.setString(i, routingDays.get(i - 1).toString());
                }
                resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    scheduledItemList.add(read(resultSet.getInt("ScheduledItemId")));
                }
            } catch (SQLException e) {
                throw new DatabaseException("Cannot read scheduled item with routing days " + routingDays, e);
            } finally {
                try {
                    if (resultSet != null) {
                        resultSet.close();
                    }
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                } catch (SQLException ex) {
                    System.err.println("Cannot close statement");
                    ex.printStackTrace(System.err);
                }
            }
        }
        return scheduledItemList;
    }

    /**
     * Найти пункты расписания в указанном диапазоне времени
     * @param now текущее время
     * @param day текущий день недели
     * @param amountBefore интервал "до"
     * @param amountAfter интервал "после"
     * @return список найденных пунктов расписания
     */
    @Override
    public List<ScheduledItem> findByTime(Time now, DaysOfWeek day, Time amountBefore, Time amountAfter) {
        List<ScheduledItem> list = findAll();
        List<ScheduledItem> scheduledItemList = new ArrayList<ScheduledItem>();
        for (ScheduledItem scheduledItem : list) {
            if (scheduledItem.getRoutingDays().contains(day)) {
                if (scheduledItem.getDepartureTime().isWithinRange(now, amountBefore, amountAfter) || scheduledItem.getArrivalTime().isWithinRange(now, amountBefore, amountAfter) || scheduledItem.getDepartureTimeFromEndPoint().isWithinRange(now, amountBefore, amountAfter) || scheduledItem.getArrivalTime().isWithinRange(now, amountBefore, amountAfter)) {
                    if (!scheduledItemList.contains(scheduledItem)) {
                        scheduledItemList.add(scheduledItem);
                    }
                }
            }
        }
        return scheduledItemList;
    }

    /**
     * Найти пункты расписания в указанном диапазоне времени (используется текущее время и день недели)
     * @param amountBefore интервал "до"
     * @param amountAfter интервал "после"
     * @return список найденных пунктов расписания
     */
    @Override
    public List<ScheduledItem> findByTime(Time amountBefore, Time amountAfter) {
        return findByTime(Time.now(), DaysOfWeek.today(), amountBefore, amountAfter);
    }

    /**
     * Найти пункты расписания по названию маршрута
     * @param routeName название маршрута
     * @return список пунктов расписания
     */
    @Override
    public List<ScheduledItem> findByRouteName(String routeName) {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<ScheduledItem> scheduledItemList = new ArrayList<ScheduledItem>();
        try {
            preparedStatement = connection.prepareStatement("SELECT Id FROM ScheduledItem INNER JOIN Route ON ScheduledItem.RouteId=Route.Id WHERE RouteName=? ORDER BY Id");
            preparedStatement.setString(1, routeName);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                ScheduledItem scheduledItem = new ScheduledItemProxy();
                scheduledItem.setId(resultSet.getInt("Id"));
                scheduledItemList.add(scheduledItem);
            }
        } catch (SQLException e) {
            throw new DatabaseException("Cannot read scheduled item with route name " + routeName, e);
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cannot close statement");
                ex.printStackTrace(System.err);
            }
        }
        return scheduledItemList;
    }
}
